import pymysql
import time
from pymysql.err import OperationalError

# 判断返回搜索结果还是数量
return_cnt_list = ['insert', 'update', 'replace', 'delete']
return_result_list = ['select']


class Mysql:
    def __init__(self, database="comment", debug=False):
        _user = 'root'
        _passwd = '123456'
        _host = 'localhost'
        self._debug = debug
        self.conn = pymysql.connect(user=_user, passwd=_passwd, db=database, host=_host, use_unicode=True,
                                    charset='utf8')
        self.cursor = self.conn.cursor()

    def execute_sql(self, sql):
        if len(sql.strip()) > 0:
            if self._debug:
                print(sql)
            cnt = self.cursor.execute(sql)
            result = self.cursor.fetchall()
            try:
                self.conn.commit()
            except OperationalError:
                time.sleep(1)
                self.conn.commit()
            for field in return_cnt_list:
                if field in sql:
                    return cnt
            for field in return_result_list:
                if field in sql:
                    if "count(*)" in sql:
                        return result[0][0]
                    return result
            raise Exception("check return type: " + sql)

    def joint_value(self, value_list):
        ret_list = []
        for value in value_list:
            ret_list.append(self.get_value(value))
        return ",".join(ret_list)

    def get_value(self, value):
        if isinstance(value, str):
            return '"' + value + '"'
        if isinstance(value, int):
            return str(value)
        else:
            print("type of value is {0}".format(type(value)))
            raise Exception("type error!")

    def list_field_dict(self, model):
        field_list, value_list = [], []
        for key, value in model.get_field_dict().items():
            if value is not None:
                field_list.append(key)
                value_list.append(value)
        return self.get_fields(field_list), self.get_values(value_list)

    def get_fields(self, field_list):
        return ",".join(field_list)

    def get_values(self, value_list):
        return self.joint_value(value_list)

    def get_clause(self, clause):
        return "where " + clause if clause is not None else ""

    def insert(self, table, model, ignore=False):
        fields, values = self.list_field_dict(model)
        return self.execute_sql("insert into {0} ({1}) values ({2}) {3}".format(table, fields, values,
                                                                                "ON DUPLICATE KEY UPDATE url=url" if ignore else ""))

    def replace_into(self, table, model):
        fields, values = self.list_field_dict(model)
        return self.execute_sql("replace into {0} ({1}) values ({2})".format(table, fields, values))

    def list_model(self, table, Model, clause=None):
        model = Model()
        fields = self.list_field_dict(model)[0]
        results = self.execute_sql("select {fields} from {table} {clause}".format(
            fields=fields, table=table, clause=self.get_clause(clause)
        ))
        ret_list = []
        for result in results:
            model = Model(result)
            ret_list.append(model)
        return ret_list

    def list(self, table, field, clause=None):
        result = self.execute_sql("select {0} from {1} {2}".format(field, table, self.get_clause(clause)))
        if "," in field:
            return result
        return [item[0] for item in result]

    def update(self, table, field, value, clause):
        if field is not None and value is not None:
            return self.execute_sql("update {0} set {1}={2} {3}".format(table, field, self.get_value(value), self.get_clause(clause)))
        return 0

    def delete(self, table, clause):
        return self.execute_sql("delete from {0} {1}".format(table, self.get_clause(clause)))

if __name__ == '__main__':
    mysql = Mysql()
    print(mysql.joint_value(['ab', 12]))
